﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using YY.DataLinq;
using System.IO;
using Artech.ContextPropagation;
using YY.Common;
using System.Data;
using System.Data.SqlClient;

namespace YY.WCFLib
{
    // 注意: 如果更改此处的类名 "BaseData"，也必须更新 App.config 中对 "BaseData" 的引用。
    public class BaseData : IBaseData
    {
        //实例化linq to sql类  
        YY.DataLinq.BaseDataDataContext dc = new YY.DataLinq.BaseDataDataContext();

        #region 获取流水号
        /// <summary>
        /// 获取流水号（单据、分类）
        /// </summary>
        /// <param name="type">1-单据,2-分类</param>
        /// <param name="billType">单据类型</param>
        /// <param name="len">自定义流水号长度</param>
        /// <param name="prefix">是否加前缀</param>
        /// <param name="fillZero">是否补零</param>
        /// <returns>流水号</returns>        
        public string GetSerialNo(int type, string billType, int len, bool prefix, bool fillZero, bool fillDate)
        {
            string code = string.Empty;
            try
            {
                dc.GetSerialNo(type, billType, len, prefix, fillZero, fillDate, ref code);
            }
            catch { }
            return code;
        }
        #endregion

        #region 用户
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="code"></param>
        /// <param name="pwd"></param>
        /// <returns>错误消息</returns>
        public LoginResult CheckLogin(string code, string pwd)
        {
            LoginResult lr = new LoginResult();
            try
            {
                var predicate = PredicateBuilder.True<TB_User>();
                predicate = predicate.And(p => !p.deleteFlag && p.code == code);

                if (dc.TB_User.Any(predicate))
                {
                    TB_User user = dc.TB_User.Single(predicate);
                    string encryptPwd = string.Empty;
                    if (pwd != string.Empty)
                        encryptPwd = YY.Common.EncryptCommon.MD5Encrypt(pwd);
                    if ((string.IsNullOrEmpty(user.pwd) && string.IsNullOrEmpty(encryptPwd)) || (user.pwd != null && user.pwd.Equals(encryptPwd)))
                    {
                        lr.User = user;
                        lr.ErrorMsg = string.Empty;
                    }
                    else
                        lr.ErrorMsg = "用户密码错误";

                }
                else
                    lr.ErrorMsg = "查无此用户信息！";
            }
            catch (Exception ex)
            {
                lr.ErrorMsg = ex.Message;
            }
            return lr;
        }

        /// <summary>
        /// 新增用户
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int AddUser(TB_User obj)
        {
            try
            {
                dc.TB_User.InsertOnSubmit(obj);
                dc.SubmitChanges();
                return obj.id;
            }
            catch { }
            return 0;
        }

        /// <summary>
        /// 修改用户资料
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool UpdateUser(TB_User obj)
        {
            try
            {
                var o = dc.TB_User.SingleOrDefault(d => d.id == obj.id);
                if (o != null)
                {
                    Common.objEval(obj, o);
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        /// <summary>
        /// 删除用户资料
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool DeleteUser(int id)
        {
            try
            {
                var o = dc.TB_User.SingleOrDefault(d => d.id == id);
                if (o != null)
                {
                    o.deleteFlag = true;
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        /// <summary>
        /// 获取单个用户资料
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public TB_User GetUser(int id)
        {
            return dc.TB_User.SingleOrDefault(d => d.id == id && !d.deleteFlag);
        }

        /// <summary>
        /// 搜索用户资料
        /// </summary>
        /// <param name="code"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        public IEnumerable<TB_User> FindUsers(string code, string userName)
        {
            try
            {
                var predicate = PredicateBuilder.True<TB_User>();
                predicate = predicate.And(p => !p.deleteFlag);

                if (!string.IsNullOrEmpty(userName))
                    predicate = predicate.And(p => p.userName.Contains(userName));

                if (!string.IsNullOrEmpty(code))
                    predicate = predicate.And(p => p.code.Contains(code));
                return dc.TB_User.Where(predicate);
            }
            catch { }
            return null;
        }

        /// <summary>
        /// 检查用户账号是否重复
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public bool ExistUserCode(string code)
        {
            return dc.TB_User.Any(m => !m.deleteFlag && m.code == code);
        }
        #endregion

        #region 客户
        public int AddClient(TB_Clients obj)
        {
            try
            {
                dc.TB_Clients.InsertOnSubmit(obj);
                dc.SubmitChanges();
                return obj.id;
            }
            catch { }
            return 0;
        }

        public bool UpdateClient(TB_Clients obj)
        {
            try
            {
                var o = dc.TB_Clients.SingleOrDefault(d => d.id == obj.id);
                if (o != null)
                {

                    Common.objEval(obj, o);
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        public bool DeleteClient(int id)
        {
            try
            {
                var o = dc.TB_Clients.SingleOrDefault(d => d.id == id);
                if (o != null)
                {
                    o.deleteFlag = true;
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        public TB_Clients GetClient(int id)
        {
            return dc.TB_Clients.SingleOrDefault(d => d.id == id && !d.deleteFlag);
        }

        public IEnumerable<TB_Clients> FindClients(string shortName, string name, string tel, string address, string contactMan)
        {
            try
            {
                var predicate = PredicateBuilder.True<TB_Clients>();
                predicate = predicate.And(p => !p.deleteFlag);

                if (!string.IsNullOrEmpty(name))
                    predicate = predicate.And(p => p.fullName.Contains(name));

                if (!string.IsNullOrEmpty(shortName))
                    predicate = predicate.And(p => p.shortName.Contains(shortName));

                if (!string.IsNullOrEmpty(tel))
                    predicate = predicate.And(p => p.tel.Contains(tel));

                if (!string.IsNullOrEmpty(contactMan))
                    predicate = predicate.And(p => p.contactMan.Contains(contactMan));

                if (!string.IsNullOrEmpty(address))
                    predicate = predicate.And(p => p.address.Contains(address));
                return dc.TB_Clients.Where(predicate);
            }
            catch { }
            return null;
        }

        public IEnumerable<TB_Clients> FindClients2(string shortName, string name, string tel, string address, string contactMan, int pageSize, int pageIndex, out int recordCount, out int pageCount)
        {
            recordCount = 0;
            pageCount = 0;
            try
            {
                var query = this.FindClients(shortName, name, tel, address, contactMan);
                recordCount = query.Count();
                if (pageSize > 0)
                {
                    pageCount = (recordCount + pageSize - 1) / pageSize;
                    var q = query.Skip(pageSize * pageIndex).Take(pageSize);
                    return q;
                }
                else
                    return query;
            }
            catch
            { }
            return null;
        }
        #endregion

        #region 通用分类管理

        /// <summary>
        /// 获取单个分类信息
        /// </summary>
        /// <param name="id">分类系统编号</param>
        /// <returns>分类对象</returns>        
        public TB_Options GetOption(int id)
        {
            return dc.TB_Options.SingleOrDefault(d => d.id == id);
        }

        /// <summary>
        /// 获取单个分类信息
        /// </summary>
        /// <param name="type">分类类型</param>
        /// <param name="name">分类名称</param>
        /// <returns>分类对象</returns>        
        public TB_Options GetOptionByTpNm(string type, string name)
        { return dc.TB_Options.SingleOrDefault(d => d.type == type && d.name == name); }

        /// <summary>
        /// 获取一类分类数据
        /// </summary>
        /// <returns>TB_Options</returns>
        public IEnumerable<TB_Options> GetOptionTree(string type)
        {
            return (from c in dc.TB_Options where c.type == type orderby c.sort select c);
        }

        /// <summary>
        /// 查找分类
        /// </summary>
        /// <param name="type">分类类型</param>
        /// <param name="code">分类代号</param>
        /// <param name="name">分类名称</param>
        /// <returns>TB_Options</returns>        
        public IEnumerable<TB_Options> FindOptions(string type, string code, string name)
        {
            try
            {
                var predicate = PredicateBuilder.True<TB_Options>();
                if (type != null)
                    predicate = predicate.And(p => p.type == type);

                if (!string.IsNullOrEmpty(name))
                    predicate = predicate.And(p => p.name.Contains(name));

                if (!string.IsNullOrEmpty(code))
                    predicate = predicate.And(p => p.code.Contains(code));
                return dc.TB_Options.Where(predicate);
            }
            catch { }
            return null;
        }

        /// <summary>
        /// 新增分类
        /// </summary>
        /// <param name="option">分类</param>
        /// <returns>新建分类系统编号</returns>
        public int AddOption(TB_Options o)
        {
            try
            {
                dc.TB_Options.InsertOnSubmit(o);
                dc.SubmitChanges();
                return o.id;
            }
            catch { }
            return 0;
        }

        /// <summary>
        /// 更新一个分类
        /// </summary>
        /// <param name="option">分类类</param>
        /// <returns>是否更新成功</returns>
        public bool UpdateOption(TB_Options option)
        {
            try
            {
                var o = dc.TB_Options.SingleOrDefault(d => d.id == option.id);
                if (o != null)
                {
                    Common.objEval(option, o);
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        /// <summary>
        /// 删除一个分类
        /// </summary>
        /// <param name="id">分类系统编号</param>
        /// <returns>是否删除成功</returns>
        public bool DeleteOption(int id)
        {
            try
            {
                var o = dc.TB_Options.SingleOrDefault(d => d.id == id);
                if (o != null)
                {
                    dc.TB_Options.DeleteOnSubmit(o);
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }
        #endregion

        #region 作业单
        public int AddOrder(TB_Order obj)
        {
            try
            {
                dc.TB_Order.InsertOnSubmit(obj);
                TB_Workflow w = new TB_Workflow();
                w.flowStatus = EnumCommon.OrderStatus.订舱录入.ToString();
                w.isHandle = false;
                w.@operator = obj.agent;
                obj.TB_Workflow.Add(w);
                dc.SubmitChanges();
                return obj.id;
            }
            catch { }
            return 0;
        }

        public bool UpdateOrder(TB_Order obj)
        {
            try
            {
                var o = dc.TB_Order.SingleOrDefault(d => d.id == obj.id);
                if (o != null)
                {
                    Common.objEval(obj, o, new List<string> { typeof(TB_Attach).Name, typeof(TB_Case).Name, typeof(TB_Customs).Name, typeof(TB_Fee).Name, typeof(TB_Trailer).Name, typeof(TB_Workflow).Name });
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        public MsgBoolean DeleteOrder(int id)
        {
            MsgBoolean mb = new MsgBoolean();
            mb.Ok = false;
            try
            {
                var o = dc.TB_Order.SingleOrDefault(d => d.id == id);
                if (o != null)
                {
                    if (o.isSubmit)
                        mb.Msg = "已提交财务审核单据不能删除！";
                    else
                    {
                        o.deleteFlag = true;
                        dc.SubmitChanges();
                        mb.Ok = true;
                    }
                }
            }
            catch (Exception ex)
            { mb.Msg = ex.Message; }
            return mb;
        }

        public TB_Order GetOrder(int id)
        {
            return dc.TB_Order.SingleOrDefault(d => d.id == id && !d.deleteFlag);
        }

        [ContextReceivalBehavior(IsBidirectional = true)]
        public IEnumerable<QOrder> FindOrders(bool? deleteFlag, string orderCode, string cCode, DateTime? sDate, DateTime? eDate,
            string orderStatus, int? agent, int? bizMan, int? stowageMethod, string clientName, bool? isDrawback,
            string customsStatus, string checkOffCode, string caseCode)
        {

            try
            {
                EnumCommon.SysRoleType loginUserRole = (EnumCommon.SysRoleType)ApplicationContext.Current.Role;
                int loginUserId = ApplicationContext.Current.UserId;

                var predicate = PredicateBuilder.True<TB_Order>();
                if (deleteFlag != null)
                    predicate = predicate.And(p => p.deleteFlag == deleteFlag);

                //if (EnumCommon.SysRoleType.财务员 == loginUserRole)
                //{ predicate = predicate.And(p => p.financier == loginUserId); }

                if (!string.IsNullOrEmpty(orderStatus))
                    predicate = predicate.And(p => p.status == orderStatus);

                if (agent != null)
                    predicate = predicate.And(p => p.agent == agent);
                if (EnumCommon.SysRoleType.客服员 == loginUserRole)
                {
                    var workflowOrderIdList = dc.TB_Workflow.Where(p => p.nextOperator == loginUserId);
                    //转派到另一客服后，转派的客服也要看到单据
                    if (workflowOrderIdList != null)
                    {
                        predicate = predicate.And(p => p.agent == loginUserId | p.@operator == loginUserId | workflowOrderIdList.Any(w => w.orderId == p.id));
                        //predicate = predicate.Or(QueryBuilder.Create<TB_Order>().In(p => p.id, workflowOrderIdList.ToArray()).Expression);                        
                    }
                    else
                        predicate = predicate.And(p => p.agent == loginUserId | p.@operator == loginUserId);
                }

                if (bizMan != null)
                    predicate = predicate.And(p => p.@operator == bizMan);
                if (EnumCommon.SysRoleType.业务员 == loginUserRole)
                    predicate = predicate.And(p => p.@operator == loginUserId);

                if (stowageMethod != null)
                    predicate = predicate.And(p => p.stowageMethod == stowageMethod);

                if (sDate != null && eDate != null)
                {
                    predicate = predicate.And(p => p.orderTime >= sDate);
                    predicate = predicate.And(p => p.orderTime <= eDate);
                }
                if (!string.IsNullOrEmpty(orderCode))
                    predicate = predicate.And(p => p.code.Contains(orderCode));
                if (!string.IsNullOrEmpty(cCode))
                    predicate = predicate.And(p => p.cCode.Contains(cCode));

                //var predicate2 = PredicateBuilder.True<TB_Customs>();
                //if (!string.IsNullOrEmpty(customsStatus))
                //    predicate2 = predicate2.And(p => p.status == customsStatus);

                var predicate1 = PredicateBuilder.True<TB_User>();
                if (EnumCommon.SysRoleType.主管经理 == loginUserRole)
                {
                    predicate1 = predicate1.And(p => p.leader == loginUserId);
                    predicate1 = predicate1.Or(p => p.id == loginUserId);
                }

                var query = from s in dc.TB_Order.Where(predicate)
                            join c in dc.TB_Customs on s.id equals c.order into t_Customs
                            from cc in t_Customs.DefaultIfEmpty()
                            //join co in dc.TB_CheckOff on cc.id equals co.customsId into t_checkoff
                            //from coo in t_checkoff.DefaultIfEmpty()
                            join c1 in dc.TB_Clients on s.client equals c1.id into t_Client
                            from cc1 in t_Client.DefaultIfEmpty()
                            join u in dc.TB_User.Where(predicate1) on s.@operator equals u.id
                            join u1 in dc.TB_User on s.agent equals u1.id
                            join p in dc.TB_Options on s.startPort equals p.id into t_StartPort
                            from pp in t_StartPort.DefaultIfEmpty()
                            //join c2 in dc.TB_Case on s.id equals c2.order into t_case
                            //from cc2 in t_case.DefaultIfEmpty()
                            where ((isDrawback == null) || (isDrawback != null && cc.isDrawback == isDrawback))
                            && ((string.IsNullOrEmpty(clientName)) || (!string.IsNullOrEmpty(clientName) && cc1.fullName.Contains(clientName)))
                            && ((string.IsNullOrEmpty(customsStatus)) || (!string.IsNullOrEmpty(customsStatus) && cc.status == customsStatus))
                            && ((string.IsNullOrEmpty(checkOffCode)) || (!string.IsNullOrEmpty(checkOffCode) && dc.TB_CheckOff.Any(p => p.customsId == cc.id && p.code.Contains(checkOffCode))))
                            && ((string.IsNullOrEmpty(caseCode)) || (!string.IsNullOrEmpty(caseCode) && dc.TB_Case.Any(p => p.order == s.id && p.code.Contains(caseCode))))
                            select new QOrder
                            {
                                OrderId = s.id,
                                OrderCode = s.code,
                                CCode = s.cCode,
                                ClientName = cc1.fullName,
                                IsOffer = cc.isOffer,
                                IsCheck = cc.isCheck,
                                IsPass = cc.isPass,
                                IsNotice = cc.isNotice,
                                OrderStatus = s.status,
                                CustomsStatus = cc.status,
                                ApplyDate = cc.applyDate,
                                Bizman = s.@operator,
                                BizmanName = u.userName,
                                Agent = s.agent,
                                AgentName = u1.userName,
                                DeleteFlag = s.deleteFlag,
                                PassTime = cc.passTime,
                                StartPort = s.startPort,
                                StartPortName = pp.name
                            };
                if (query.Any())
                    return query.OrderByDescending(p => p.OrderId);

            }
            catch { }
            return null;
        }

        public IEnumerable<QOrder> FindOrders2(bool? deleteFlag, string orderCode, string cCode, DateTime? sDate, DateTime? eDate,
            string orderStatus, int? agent, int? bizMan, int? stowageMethod, string clientName, bool? isDrawback,
            string customsStatus, string checkOffCode, string caseCode, int loginUserId, int loginUserRole, string sort, string order, int pageSize, int pageIndex, out int recordCount, out int pageCount)
        {
            recordCount = 0;
            pageCount = 0;
            try
            {
                ApplicationContext.Current.Role = loginUserRole;
                ApplicationContext.Current.UserId = loginUserId;
                var query = this.FindOrders(deleteFlag, orderCode, cCode, sDate, eDate, orderStatus, agent, bizMan, stowageMethod, clientName, isDrawback, customsStatus, checkOffCode, caseCode);
                recordCount = query.Count();
                pageCount = (recordCount + pageSize - 1) / pageSize;
                if (pageSize > 0)
                {
                    if (!string.IsNullOrEmpty(sort))
                    {
                        if (order == "desc")
                        {
                            var q = from s in query
                                    orderby typeof(QOrder).GetProperty(sort).GetValue(s, null) descending
                                    select s;
                            return q;
                        }
                        else
                        {
                            var q = from s in query
                                    orderby typeof(QOrder).GetProperty(sort).GetValue(s, null) ascending
                                    select s;
                            return q;
                        }

                    }
                    else
                    {
                        var q = query.Skip(pageSize * pageIndex).Take(pageSize);
                        return q;
                    }
                }
                else
                    return query;
            }
            catch
            { }
            return null;
        }

        /// <summary>
        /// 检测单号是否重复存在
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="orderCode"></param>
        /// <param name="cCode"></param>
        /// <returns></returns>
        public bool ExistsOrderCode(int? orderId, string orderCode, string cCode)
        {
            try
            {
                var predicate = PredicateBuilder.True<TB_Order>();
                predicate = predicate.And(p => p.deleteFlag == false);
                if (orderId.HasValue)
                    predicate = predicate.And(p => p.id != orderId.Value);
                if (!string.IsNullOrEmpty(orderCode))
                    predicate = predicate.And(p => p.code.ToLower() == orderCode.ToLower());
                if (!string.IsNullOrEmpty(cCode))
                    predicate = predicate.And(p => p.cCode.ToLower() == cCode.ToLower());

                return dc.TB_Order.Any(predicate);

            }
            catch { }
            return false;
        }

        public DataTable GetOrderBill(int targetId, DateTime sDate, DateTime eDate, int feeType)
        {

            try
            {
                //string items = string.Empty;


                //if ((int)EnumCommon.FeeType.应付 == feeType)
                //{
                //    var query = from s in dc.TB_Fee.Where(p => p.feeType == feeType)
                //                join o in dc.TB_Options on s.item equals o.id
                //                join o1 in dc.TB_Order on s.order equals o1.id
                //                join o2 in dc.TB_Options on s.target equals o2.id
                //                where s.target == targetId && o1.orderTime >= sDate && o1.orderTime <= eDate && !o1.deleteFlag
                //                select o.name;
                //    items = string.Join(",", query.Distinct().ToArray());
                //}
                //else
                //{
                //    var query = from s in dc.TB_Fee.Where(p => p.feeType == feeType)
                //                join o in dc.TB_Options on s.item equals o.id
                //                join o1 in dc.TB_Order on s.order equals o1.id
                //                where o1.client == targetId && o1.orderTime >= sDate && o1.orderTime <= eDate && !o1.deleteFlag
                //                select o.name;
                //    items = string.Join(",", query.Distinct().ToArray());
                //}

                //if ((int)EnumCommon.FeeType.应付 == feeType)
                //{
                //    var query = from s in dc.TB_Fee.Where(p => p.feeType == feeType)
                //                join o in dc.TB_Options on s.item equals o.id
                //                join o1 in dc.TB_Order on s.order equals o1.id
                //                join o2 in dc.TB_Options on s.target equals o2.id
                //                where s.target == targetId && o1.orderTime >= sDate && o1.orderTime <= eDate && !o1.deleteFlag
                //                select o.name;
                //    items = string.Join("],[", query.Distinct().ToArray());
                //}
                //else
                //{
                //    var query = from s in dc.TB_Fee.Where(p => p.feeType == feeType)
                //                join o in dc.TB_Options on s.item equals o.id
                //                join o1 in dc.TB_Order on s.order equals o1.id
                //                where o1.client == targetId && o1.orderTime >= sDate && o1.orderTime <= eDate && !o1.deleteFlag
                //                select o.name;
                //    items = string.Join("],[", query.Distinct().ToArray());
                //}

                //if (!string.IsNullOrEmpty(items))
                //    items = "[" + items + "]";

                StringBuilder sb = new StringBuilder();
                //sb.AppendLine("SELECT m.*,n.*,t.*");
                sb.AppendLine("SELECT m.cCode [订舱号] ,m.xx [箱型] ,m.xh [箱号] ,m.fh [封号] ,m.address [装货地点] ,CONVERT(VARCHAR(12), m.deadline, 111) [装货日期] ,m.fy [费用] ,t.* ,m.[memo] [备注]");
                sb.AppendLine(" from ");
                sb.AppendLine("(SELECT  o.cCode ,");
                sb.AppendLine("        '' AS xx ,");
                sb.AppendLine("        '' AS xh ,");
                sb.AppendLine("        '' AS fh ,");
                sb.AppendLine("        t.address ,");
                sb.AppendLine("        t.deadline ,");
                sb.AppendLine("        '' AS fy ,");
                sb.AppendLine("        o.id ,");
                sb.AppendLine("        o.memo");
                sb.AppendLine("FROM    dbo.TB_Order o ");
                sb.AppendLine("        LEFT OUTER JOIN dbo.TB_Trailer t ON o.id = t.orderId");
                sb.AppendLine("WHERE   o.deleteFlag=0");

                if ((int)EnumCommon.FeeType.应收 == feeType)
                    sb.AppendLine("        AND o.client=" + targetId.ToString());

                sb.AppendLine("        AND o.orderTime>='" + sDate.ToString("yyyy-MM-dd HH:mm:ss") + "'");
                sb.AppendLine("        AND o.orderTime<='" + eDate.ToString("yyyy-MM-dd HH:mm:ss") + "'");
                sb.AppendLine("        ) m");
                //sb.AppendLine("        INNER JOIN ");
                //sb.AppendLine("        (");
                //sb.AppendLine("        SELECT  a.* ");
                //sb.AppendLine("FROM    ( SELECT    o.name ,");
                //sb.AppendLine("                    f.amount,o1.id");
                //sb.AppendLine("          FROM      dbo.TB_Fee f ,");
                //sb.AppendLine("                    dbo.TB_Options o,");
                //sb.AppendLine("                    dbo.TB_Order o1");
                //sb.AppendLine("          WHERE     f.item = o.id and f.[order]=o1.id");

                //if ((int)EnumCommon.FeeType.应付 == feeType)
                //    sb.AppendLine("                    AND f.target=" + targetId.ToString());

                //sb.AppendLine("                    AND f.isVerify=1 AND f.feeType = " + feeType.ToString());
                //sb.AppendLine("        ) tb PIVOT( MAX(tb.amount) FOR tb.name IN ( " + items + " ) ) a");
                //sb.AppendLine("        ) n ON m.id = n.id");
                sb.AppendLine("            INNER JOIN    ");
                sb.AppendLine("        (");
                sb.AppendLine("        SELECT SUM(amount) 总额,[order] orderId FROM dbo.TB_Fee");
                //sb.AppendLine("        WHERE isVerify=1 ");
                sb.AppendLine("        WHERE 1=1 ");

                if ((int)EnumCommon.FeeType.应付 == feeType)
                    sb.AppendLine("                    AND target=" + targetId.ToString());

                sb.AppendLine("        and feeType=" + feeType.ToString() + " group by [order]");
                sb.AppendLine("        ) t ON m.id=t.orderId");

                return Common.GetSqlDataTable(dc.Connection.ConnectionString, sb.ToString());
            }
            catch { }
            return null;
        }
        #endregion

        #region 工作流
        public int AddWorkflow(TB_Workflow obj)
        {
            try
            {
                dc.TB_Workflow.InsertOnSubmit(obj);
                dc.SubmitChanges();
                return obj.id;
            }
            catch { }
            return 0;
        }

        [ContextReceivalBehavior(IsBidirectional = true)]
        public MsgBoolean HandleWorkflow(int orderId, int opType, int? nextOp, string nextOpStatus, string comment)
        {
            MsgBoolean mb = new MsgBoolean();
            try
            {
                int loginUserRole = ApplicationContext.Current.Role;
                int loginUserId = ApplicationContext.Current.UserId;
                bool? ok = false;
                string msg = string.Empty;
                dc.HandleWorkflow(orderId, loginUserId, loginUserRole, opType, nextOp, nextOpStatus, comment, ref ok, ref msg);
                mb.Ok = Convert.ToBoolean(ok);
                mb.Msg = msg;
                //var w = dc.TB_Workflow.SingleOrDefault(d => d.id == obj.id);
                //if (w != null)
                //{
                //    var o = dc.TB_Order.SingleOrDefault(d => d.id == w.orderId);

                //    if (o.status == EnumCommon.OrderStatus.操作完成.ToString() && w.flowStatus == EnumCommon.OrderStatus.财务审核.ToString())
                //        o.isSubmit = true;
                //    else if (o.status == EnumCommon.OrderStatus.已出账单.ToString() && w.flowStatus == EnumCommon.OrderStatus.已收款.ToString())
                //        o.isClosed = true;
                //    else if (o.status == EnumCommon.OrderStatus.财务审核.ToString() && w.flowStatus == EnumCommon.OrderStatus.操作完成.ToString())
                //        o.isSubmit = false;
                //    else if (o.status == EnumCommon.OrderStatus.已收款.ToString() && w.flowStatus == EnumCommon.OrderStatus.已出账单.ToString())
                //        o.isClosed = false;
                //    o.status = w.flowStatus;
                //    Common.objEval(obj, w, new List<string> { typeof(TB_Order).Name });
                //    dc.SubmitChanges();
                //    return true;
                //}
            }
            catch (Exception ex)
            {
                mb.Ok = false;
                mb.Msg = ex.Message;
            }

            return mb;
        }

        public MsgBoolean HandleWorkflow2(int orderId, int opType, int? nextOp, string nextOpStatus, string comment, int loginUserId, int loginUserRole)
        {
            MsgBoolean mb = new MsgBoolean();
            try
            {
                ApplicationContext.Current.Role = loginUserRole;
                ApplicationContext.Current.UserId = loginUserId;
                return this.HandleWorkflow(orderId, opType, nextOp, nextOpStatus, comment);
            }
            catch (Exception ex)
            { mb.Msg = ex.Message; }
            return mb;
        }

        public TB_Workflow GetCurrentWorkflow(TB_Order order)
        {
            try
            {
                var query = dc.TB_Workflow.Where(p => p.orderId == order.id);
                return query.SingleOrDefault(p => p.id == query.Max(q => q.id));
            }
            catch { }
            return null;
        }

        public IEnumerable<QWorkflow> FindWorkflow(int orderId)
        {
            try
            {
                var predicate = PredicateBuilder.True<TB_Workflow>();
                predicate = predicate.And(p => p.orderId == orderId);
                var query = from s in dc.TB_Workflow.Where(predicate)
                            join o in dc.TB_User on s.@operator equals o.id into t_Operator
                            from oo in t_Operator.DefaultIfEmpty()
                            join n in dc.TB_User on s.nextOperator equals n.id into t_NextOperator
                            from nn in t_Operator.DefaultIfEmpty()
                            select new QWorkflow
                            {
                                Id = s.id,
                                OrderId = s.orderId,
                                FlowStatus = s.flowStatus,
                                Operator = oo.userName,
                                Comment = s.comment,
                                OPTime = s.opTime,
                                NextOperator = nn.userName,
                                HandleText = s.isHandle ? "已处理" : "未处理"
                            };
                if (query.Any())
                    return query;

            }
            catch { }
            return null;
        }
        #endregion

        #region 拖车信息
        public int AddTrailer(TB_Trailer obj)
        {
            try
            {
                dc.TB_Trailer.InsertOnSubmit(obj);
                dc.SubmitChanges();
                return obj.id;
            }
            catch { }
            return 0;
        }

        public bool UpdateTrailer(int orderId, IEnumerable<TB_Trailer> objs)
        {
            try
            {
                var order = dc.TB_Order.SingleOrDefault(d => d.id == orderId);
                if (order != null)
                {
                    dc.TB_Trailer.DeleteAllOnSubmit(order.TB_Trailer);
                    if (objs != null)
                    {
                        string feeItemName = dc.TB_SysParams.SingleOrDefault(p => p.paramName == EnumCommon.SysParamType.TCFeeItemName.ToString()).paramValue;
                        string feeItemType = EnumCommon.OptionType.费目类型.ToString();
                        short feeType = (short)EnumCommon.FeeType.应付;
                        string defualtUnit = dc.TB_SysParams.SingleOrDefault(p => p.paramName == EnumCommon.SysParamType.DefaultUnit.ToString()).paramValue;
                        TB_Options tcOption = dc.TB_Options.SingleOrDefault(q => q.name == feeItemName && q.type == feeItemType);
                        int tcFeeId = 0;
                        //添加“拖车费”到应付费用
                        if (tcOption != null)
                            tcFeeId = tcOption.id;
                        else
                        {
                            TB_Options o = new TB_Options();
                            o.name = feeItemName;
                            o.isSys = false;
                            o.parentId = 0;
                            o.type = feeItemType;
                            o.code = string.Empty;
                            dc.TB_Options.InsertOnSubmit(o);
                            dc.SubmitChanges();
                            tcFeeId = o.id;
                        }
                        //tcFeeId = dc.TB_Options.SingleOrDefault(q => q.name == feeItemName && q.type == feeItemType).id;

                        foreach (TB_Trailer t in objs)
                        {

                            if (t.charge != null)
                            {
                                if (!dc.TB_Fee.Any(p => p.feeType == feeType && p.item == tcFeeId))
                                {
                                    int? target = t.agent;
                                    TB_Options agentOption = dc.TB_Options.SingleOrDefault(q => q.id == t.agent && q.type == EnumCommon.OptionType.拖车行.ToString());
                                    if (agentOption != null)//新增应付结算对象
                                    {
                                        if (dc.TB_Options.Any(yfTarget => yfTarget.type == EnumCommon.OptionType.结算对象.ToString() && yfTarget.name == agentOption.name))
                                            target = dc.TB_Options.SingleOrDefault(to => to.type == EnumCommon.OptionType.结算对象.ToString() && to.name == agentOption.name).id;
                                        else
                                        {
                                            TB_Options newTarget = new TB_Options();
                                            newTarget.isSys = false;
                                            newTarget.name = agentOption.name;
                                            newTarget.parentId = 0;
                                            newTarget.sort = 0;
                                            newTarget.type = EnumCommon.OptionType.结算对象.ToString();
                                            newTarget.code = string.Empty;
                                            dc.TB_Options.InsertOnSubmit(newTarget);
                                            dc.SubmitChanges();
                                            target = newTarget.id;
                                        }
                                    }

                                    TB_Fee f = new TB_Fee();
                                    f.amount = Convert.ToDecimal(t.charge);
                                    f.createTime = DateTime.Now;
                                    f.feeType = feeType;
                                    f.isVerify = false;
                                    f.item = tcFeeId;
                                    f.@operator = order.agent;
                                    f.opTime = DateTime.Now;
                                    f.order = order.id;
                                    f.target = target;
                                    f.unit = dc.TB_Options.SingleOrDefault(u => u.name == defualtUnit && u.type == EnumCommon.OptionType.币制单位.ToString()).id;
                                    dc.TB_Fee.InsertOnSubmit(f);
                                }
                            }

                            TB_Trailer nt = new TB_Trailer();
                            Common.objEval(t, nt, new List<string> { typeof(TB_Order).Name });
                            order.TB_Trailer.Add(nt);

                        }
                    }
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        public TB_Trailer GetTrailer(int id)
        {
            return dc.TB_Trailer.SingleOrDefault(d => d.orderId == id);
        }
        #endregion

        #region 报关信息

        public int AddCustoms(TB_Customs obj)
        {
            try
            {
                dc.TB_Customs.InsertOnSubmit(obj);
                dc.SubmitChanges();
                return obj.id;
            }
            catch { }
            return 0;
        }

        public bool UpdateCustoms(int orderId, IEnumerable<TB_Customs> objs)
        {
            try
            {
                var order = dc.TB_Order.SingleOrDefault(d => d.id == orderId);
                if (order != null)
                {
                    foreach (TB_Customs c in order.TB_Customs)//清除原明细数据
                    {
                        //c.TB_CheckOff.Clear();
                        dc.TB_CheckOff.DeleteAllOnSubmit(c.TB_CheckOff);
                    }

                    dc.TB_Customs.DeleteAllOnSubmit(order.TB_Customs);
                    if (objs != null)
                    {
                        foreach (TB_Customs c in objs)//更新明细数据
                        {
                            TB_Customs nc = new TB_Customs();
                            Common.objEval(c, nc, new List<string> { typeof(TB_CheckOff).Name, typeof(TB_Order).Name });
                            if (c.TB_CheckOff != null)
                            {
                                foreach (TB_CheckOff co in c.TB_CheckOff)
                                {
                                    TB_CheckOff nco = new TB_CheckOff();
                                    Common.objEval(co, nco, new List<string> { typeof(TB_Customs).Name });
                                    nc.TB_CheckOff.Add(nco);
                                }
                            }
                            order.TB_Customs.Add(nc);
                        }
                    }

                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        /// <summary>
        /// 报关对象更新
        /// </summary>
        /// <param name="customsId"></param>
        /// <returns></returns>
        public bool UpdateCustomsModel(TB_Customs obj, int loginUserRole)
        {
            try
            {
                //if (EnumCommon.SysRoleType.财务员 == (EnumCommon.SysRoleType)Enum.ToObject(typeof(EnumCommon.SysRoleType), loginUserRole))//只有财务人员才可以使用该接口
                //{
                    var o = dc.TB_Customs.SingleOrDefault(d => d.id == obj.id);
                    if (o != null)
                    {
                        o.status = obj.status;
                       // Common.objEval(obj, o, new List<string> { typeof(TB_CheckOff).Name, typeof(TB_Order).Name});
                        dc.SubmitChanges();
                        return true;
                    }
                //}
            }
            catch { }
            return false;
        }

        public TB_Customs GetCustoms(int id)
        { return dc.TB_Customs.SingleOrDefault(d => d.order == id); }

        public IEnumerable<TB_CheckOff> GetCheckOffs(int id)
        {
            var predicate = PredicateBuilder.True<TB_CheckOff>();
            predicate = predicate.And(p => p.customsId == id);
            return dc.TB_CheckOff.Where(predicate);
        }
        #endregion

        #region 货物信息

        public int AddCase(TB_Case obj)
        {
            try
            {
                dc.TB_Case.InsertOnSubmit(obj);
                dc.SubmitChanges();
                return obj.id;
            }
            catch { }
            return 0;
        }

        public bool UpdateCase(TB_Case obj)
        {
            try
            {
                var o = dc.TB_Case.SingleOrDefault(d => d.id == obj.id);
                if (o != null)
                {
                    Common.objEval(obj, o, new List<string> { typeof(TB_Cargo).Name, typeof(TB_Order).Name });
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        public bool DeleteCase(int id)
        {
            try
            {
                var o = dc.TB_Case.SingleOrDefault(d => d.id == id);
                if (o != null)
                {
                    dc.TB_Cargo.DeleteAllOnSubmit(o.TB_Cargo);//先清除原有的Cargos
                    dc.TB_Case.DeleteOnSubmit(o);
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }


        public TB_Case GetCase(int id)
        {
            return dc.TB_Case.SingleOrDefault(p => p.id == id);
        }


        public int AddCargo(TB_Cargo obj)
        {
            try
            {
                dc.TB_Cargo.InsertOnSubmit(obj);
                dc.SubmitChanges();
                return obj.id;
            }
            catch { }
            return 0;
        }

        public bool UpdateCargo(TB_Cargo obj)
        {
            try
            {
                var o = dc.TB_Cargo.SingleOrDefault(d => d.id == obj.id);
                if (o != null)
                {
                    Common.objEval(obj, o, new List<string> { typeof(TB_Case).Name });
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        public bool DeleteCargo(int id)
        {
            try
            {
                var o = dc.TB_Cargo.SingleOrDefault(d => d.id == id);
                if (o != null)
                {
                    dc.TB_Cargo.DeleteOnSubmit(o);
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        public TB_Cargo GetCargo(int id)
        { return dc.TB_Cargo.SingleOrDefault(p => p.id == id); }

        public IEnumerable<TB_Case> GetCases(int id)
        {
            var predicate = PredicateBuilder.True<TB_Case>();
            predicate = predicate.And(p => p.order == id);
            return dc.TB_Case.Where(predicate);
        }

        public IEnumerable<TB_Cargo> GetCargos(int id)
        {
            var predicate = PredicateBuilder.True<TB_Cargo>();
            predicate = predicate.And(p => p.@case == id);
            return dc.TB_Cargo.Where(predicate);
        }

        public IEnumerable<XCargo> GetXCargos(int id)
        {
            var predicate = PredicateBuilder.True<TB_Cargo>();
            predicate = predicate.And(p => p.@case == id);
            var query = from s in dc.TB_Cargo.Where(predicate)
                        join p in dc.TB_Options on s.packageType equals p.id into t_options
                        from pp in t_options.DefaultIfEmpty()
                        select new XCargo
                        {
                            amount = s.amount,
                            cargoName = s.cargoName,
                            @case = s.@case,
                            caseAmount = s.caseAmount,
                            hairWeight = s.hairWeight,
                            id = s.id,
                            marker = s.marker,
                            memo = s.memo,
                            netWeight = s.netWeight,
                            packageType = s.packageType,
                            volume = s.volume,
                            PackageTypeName = pp.name
                        };
            return query;
        }

        public bool UpdateCases(int orderId, IEnumerable<TB_Case> objs)
        {
            try
            {
                var order = dc.TB_Order.SingleOrDefault(d => d.id == orderId);
                if (order != null)
                {
                    foreach (TB_Case c in order.TB_Case)//清除原明细数据
                        dc.TB_Cargo.DeleteAllOnSubmit(c.TB_Cargo);
                    dc.TB_Case.DeleteAllOnSubmit(order.TB_Case);

                    if (objs != null)
                    {
                        foreach (TB_Case c in objs)//更新明细数据
                        {
                            TB_Case nc = new TB_Case();
                            Common.objEval(c, nc, new List<string> { typeof(TB_Cargo).Name, typeof(TB_Order).Name });
                            if (c.TB_Cargo != null)
                            {
                                foreach (TB_Cargo cg in c.TB_Cargo)
                                {
                                    TB_Cargo ncg = new TB_Cargo();
                                    Common.objEval(cg, ncg, new List<string> { typeof(TB_Case).Name });
                                    nc.TB_Cargo.Add(ncg);
                                }
                            }
                            order.TB_Case.Add(nc);
                        }
                    }
                }
            }
            catch { }
            return false;
        }
        #endregion

        #region 附件信息
        public int AddAttach(TB_Attach obj)
        {
            try
            {
                dc.TB_Attach.InsertOnSubmit(obj);
                dc.SubmitChanges();
                return obj.id;
            }
            catch { }
            return 0;
        }

        public bool DeleteAttach(int id)
        {
            try
            {
                var o = dc.TB_Attach.SingleOrDefault(d => d.id == id);
                if (o != null)
                {
                    string dir = Common.GetUploadFileDir(o.createTime);//文件目录
                    string filePath = System.IO.Path.Combine(dir, o.attachName);//获取文件的路径,已经保存的文件名
                    if (System.IO.File.Exists(filePath))
                        System.IO.File.Delete(filePath);
                    dc.TB_Attach.DeleteOnSubmit(o);
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        public TB_Attach GetAttach(int id)
        {
            return dc.TB_Attach.SingleOrDefault(p => p.id == id);
        }

        public IEnumerable<TB_Attach> GetAttachs(int id)
        {
            var predicate = PredicateBuilder.True<TB_Attach>();
            predicate = predicate.And(p => p.order == id);
            return dc.TB_Attach.Where(predicate);
        }

        public TransferFileInfo UploadAttach(TransferFileInfo file)
        {
            try
            {
                string dir = Common.GetUploadFileDir(file.CreateTime);//文件目录                
                // create output folder, if does not exist
                if (!System.IO.Directory.Exists(dir))
                    System.IO.Directory.CreateDirectory(dir);
                string filePath = System.IO.Path.Combine(dir, file.Name);//获取文件的路径,已经保存的文件名
                //// kill target file, if already exists
                //if (System.IO.File.Exists(filePath))
                //    System.IO.File.Delete(filePath);

                using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate))//打开文件
                {
                    long offset = file.Offset;  //file.Offset 文件偏移位置,表示从这个位置开始进行后面的数据添加
                    using (BinaryWriter writer = new BinaryWriter(fs))//初始化文件写入器
                    {
                        writer.Seek((int)offset, SeekOrigin.Begin);//设置文件的写入位置
                        writer.Write(file.Data);//写入数据

                        file.Offset = fs.Length;//返回追加数据后的文件位置
                        file.Data = null;
                        writer.Close();
                    }
                    fs.Close();
                }

                return file;
            }
            catch { }
            return null;
        }

        /// <summary>
        /// 根据文件名寻找文件是否存在
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>        
        public RemoteFileInfo DownloadAttach(DownloadRequest request)
        {
            try
            {
                var attacth = dc.TB_Attach.SingleOrDefault(p => p.id == request.ID);
                if (attacth != null)
                {
                    string dir = Common.GetUploadFileDir(attacth.createTime);//文件目录  
                    string filePath = System.IO.Path.Combine(dir, attacth.attachName);
                    if (File.Exists(filePath))
                    {
                        //using (FileStream fs = new FileStream(filePath, FileMode.Open,FileAccess.Read, FileShare.Read))
                        //{
                        //    byte[] buffer = new byte[fs.Length];
                        //    fs.Read(buffer, 0, (int)fs.Length);
                        //    TransferFileInfo file = new TransferFileInfo();
                        //    file.Name = attacth.attachName;
                        //    file.Offset = fs.Length;
                        //    file.CreateTime = attacth.createTime;
                        //    file.Length = (long)attacth.size;
                        //    file.Data = buffer;
                        //    fs.Close();
                        //    return file;
                        //}
                        // get some info about the input file                        
                        System.IO.FileInfo fileInfo = new System.IO.FileInfo(filePath);

                        // report start
                        //Console.WriteLine("Sending stream " + attacth.attachName + " to client");
                        //Console.WriteLine("Size " + fileInfo.Length);

                        // check if exists
                        //if (!fileInfo.Exists) throw new System.IO.FileNotFoundException("File not found", attacth.attachName);

                        // open stream
                        System.IO.FileStream stream = new System.IO.FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);

                        // return result
                        RemoteFileInfo file = new RemoteFileInfo();
                        file.ID = attacth.id;
                        file.Length = fileInfo.Length;
                        file.FileByteStream = stream;

                        // after returning to the client download starts. Stream remains open and on server and the client reads it, although the execution of this method is completed.
                        //TransferFileInfo file = new TransferFileInfo();
                        //file.ByteStream = stream;
                        //file.Name = attacth.attachName;
                        //file.CreateTime = attacth.createTime;
                        //file.Length = fileInfo.Length;
                        //file.Extension = fileInfo.Extension;                        
                        return file;
                    }
                }
            }
            catch { }
            return null;
        }
        #endregion

        #region 费用信息

        public int AddFee(TB_Fee obj)
        {
            try
            {
                dc.TB_Fee.InsertOnSubmit(obj);
                dc.SubmitChanges();
                return obj.id;
            }
            catch { }
            return 0;
        }

        public bool UpdateFee(TB_Fee obj)
        {
            try
            {
                var o = dc.TB_Fee.SingleOrDefault(d => d.id == obj.id);
                if (o != null)
                {
                    Common.objEval(obj, o, new List<string> { typeof(TB_Order).Name });
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        public bool DeleteFee(int id)
        {
            try
            {
                var o = dc.TB_Fee.SingleOrDefault(d => d.id == id);
                if (o != null)
                {
                    dc.TB_Fee.DeleteOnSubmit(o);
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        public TB_Fee GetFee(int id)
        { return dc.TB_Fee.SingleOrDefault(p => p.id == id); }

        public IEnumerable<TB_Fee> GetFees(int id)
        {
            var predicate = PredicateBuilder.True<TB_Fee>();
            predicate = predicate.And(p => p.order == id);
            return dc.TB_Fee.Where(predicate);
        }

        public IEnumerable<XFee> GetXFees(int id)
        {
            var predicate = PredicateBuilder.True<TB_Fee>();
            predicate = predicate.And(p => p.order == id);
            var query = from s in dc.TB_Fee.Where(predicate)
                        join o1 in dc.TB_Options on s.item equals o1.id
                        join o2 in dc.TB_Options on s.unit equals o2.id into to2
                        from oo2 in to2.DefaultIfEmpty()
                        join o3 in dc.TB_Options on s.target equals o3.id into to3
                        from oo3 in to3.DefaultIfEmpty()
                        join c in dc.TB_Clients on s.target equals c.id into tClient
                        from cc in tClient.DefaultIfEmpty()
                        select new XFee
                        {
                            amount = s.amount,
                            code = s.code,
                            createTime = s.createTime,
                            feeType = s.feeType,
                            id = s.id,
                            isVerify = s.isVerify,
                            item = s.item,
                            memo = s.memo,
                            @operator = s.@operator,
                            opTime = s.opTime,
                            order = s.order,
                            target = s.target,
                            unit = s.unit,
                            verifyMan = s.verifyMan,
                            verifyTime = s.verifyTime,
                            ItemName = o1.name,
                            UnitName = oo2.name,
                            TargetName = s.feeType == (short)EnumCommon.FeeType.应收 ? cc.fullName : oo3.name,
                        };
            return query;
        }

        public bool UpdateFees(int orderId, IEnumerable<TB_Fee> objs)
        {
            try
            {
                var order = dc.TB_Order.SingleOrDefault(d => d.id == orderId);
                if (order != null)
                {
                    dc.TB_Fee.DeleteAllOnSubmit(order.TB_Fee);
                    if (objs != null)
                    {
                        foreach (TB_Fee t in objs)
                        {
                            TB_Fee nt = new TB_Fee();
                            Common.objEval(t, nt, new List<string> { typeof(TB_Order).Name });
                            order.TB_Fee.Add(nt);
                        }
                    }
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        [ContextReceivalBehavior(IsBidirectional = true)]
        public IEnumerable<QFee> FindFees(string orderCode, string orderStatus, int? bizMan, int? op, DateTime? sDate, DateTime? eDate, int? feeType, string feeCode, int? item, int? target, bool? isVerify)
        {
            try
            {
                EnumCommon.SysRoleType loginUserRole = (EnumCommon.SysRoleType)ApplicationContext.Current.Role;
                int loginUserId = ApplicationContext.Current.UserId;

                var predicate = PredicateBuilder.True<TB_Order>();
                predicate = predicate.And(p => !p.deleteFlag);

                if (EnumCommon.SysRoleType.财务员 == loginUserRole)
                { predicate = predicate.And(p => p.financier == loginUserId); }

                if (!string.IsNullOrEmpty(orderStatus))
                    predicate = predicate.And(p => p.status == orderStatus);
                if (bizMan != null)
                    predicate = predicate.And(p => p.@operator == bizMan);
                if (!string.IsNullOrEmpty(orderCode))
                    predicate = predicate.And(p => p.code.Contains(orderCode));

                var predicate1 = PredicateBuilder.True<TB_Fee>();
                if (op != null)
                    predicate1 = predicate1.And(p => p.@operator == op);
                if (feeType != null)
                    predicate1 = predicate1.And(p => p.feeType == feeType);
                if (target != null)
                    predicate1 = predicate1.And(p => p.target == target);
                if (isVerify != null)
                    predicate1 = predicate1.And(p => p.isVerify == isVerify);
                if (sDate != null && eDate != null)
                {
                    predicate1 = predicate1.And(p => p.opTime >= sDate);
                    predicate1 = predicate1.And(p => p.opTime <= eDate);
                }
                if (!string.IsNullOrEmpty(feeCode))
                    predicate1 = predicate1.And(p => p.code.Contains(feeCode));
                if (item != null)
                    predicate1 = predicate1.And(p => p.item == item);
                var query = from s in dc.TB_Order.Where(predicate)
                            join f in dc.TB_Fee.Where(predicate1) on s.id equals f.order
                            join o in dc.TB_User on f.@operator equals o.id
                            join i in dc.TB_Options on f.item equals i.id
                            join u in dc.TB_Options on f.unit equals u.id
                            select new QFee
                            {
                                Id = f.id,
                                OrderId = s.id,
                                OrderCode = s.code,
                                OrderStatus = s.status,
                                BizMan = s.@operator,
                                FeeType = Enum.GetName(typeof(EnumCommon.FeeType), f.feeType),
                                FeeTypeId = f.feeType,
                                IsVerify = f.isVerify,
                                Item = f.item,
                                FeeCode = f.code,
                                Operator = f.@operator,
                                OPTime = f.opTime,
                                Amount = f.amount,
                                Unit = f.unit,
                                OperatorName = o.userName,
                                BizName = o.userName,
                                ItemName = i.name,
                                UnitName = u.name
                            };
                if (query.Any())
                    return query;
            }
            catch { }
            return null;
        }

        public IEnumerable<QFee> FindFees2(string orderCode, string orderStatus, int? bizMan, int? op, DateTime? sDate, DateTime? eDate, int? feeType, string feeCode, int? item, int? target, bool? isVerify,
            int loginUserId, int loginUserRole, int pageSize, int pageIndex, out int recordCount, out int pageCount)
        {
            recordCount = 0;
            pageCount = 0;
            try
            {
                ApplicationContext.Current.Role = loginUserRole;
                ApplicationContext.Current.UserId = loginUserId;
                var query = this.FindFees(orderCode, orderStatus, bizMan, op, sDate, eDate, feeType, feeCode, item, target, isVerify);
                recordCount = query.Count();
                pageCount = (recordCount + pageSize - 1) / pageSize;
                var q = query.Skip(pageSize * pageIndex).Take(pageSize);
                return q;
            }
            catch
            { }
            return null;
        }
        #endregion

        #region 收支信息

        public int AddBalance(TB_Balance obj)
        {
            try
            {
                dc.TB_Balance.InsertOnSubmit(obj);
                dc.SubmitChanges();
                return obj.id;
            }
            catch { }
            return 0;
        }

        public bool UpdateBalance(TB_Balance obj)
        {
            try
            {
                var o = dc.TB_Balance.SingleOrDefault(d => d.id == obj.id);
                if (o != null)
                {
                    Common.objEval(obj, o);
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        public bool DeleteBalance(int id)
        {
            try
            {
                var o = dc.TB_Balance.SingleOrDefault(d => d.id == id);
                if (o != null)
                {
                    dc.TB_Balance.DeleteOnSubmit(o);
                    dc.SubmitChanges();
                    return true;
                }
            }
            catch { }
            return false;
        }

        public TB_Balance GetBalance(int id)
        { return dc.TB_Balance.SingleOrDefault(p => p.id == id); }

        [ContextReceivalBehavior(IsBidirectional = true)]
        public IEnumerable<TB_Balance> FindBalances(DateTime? sDate, DateTime? eDate, int? target, int? balanceType, string bank)
        {
            try
            {
                EnumCommon.SysRoleType loginUserRole = (EnumCommon.SysRoleType)ApplicationContext.Current.Role;
                int loginUserId = ApplicationContext.Current.UserId;

                var predicate = PredicateBuilder.True<TB_Balance>();
                if (balanceType.HasValue)
                    predicate = predicate.And(p => p.bType == balanceType.Value);
                if (!string.IsNullOrEmpty(bank))
                    predicate = predicate.And(p => p.bank.Contains(bank));
                if (target.HasValue)
                    predicate = predicate.And(p => p.target == target.Value);
                if (sDate != null && eDate != null)
                {
                    predicate = predicate.And(p => p.bDate >= sDate);
                    predicate = predicate.And(p => p.bDate <= eDate);
                }
                var query = from s in dc.TB_Balance.Where(predicate)
                            select s;
                return query;
            }
            catch { }
            return null;
        }

        public IEnumerable<TB_Balance> FindBalances2(DateTime? sDate, DateTime? eDate, int? target, int? balanceType, string bank, int loginUserId, int loginUserRole, int pageSize, int pageIndex, out int recordCount, out int pageCount)
        {
            recordCount = 0;
            pageCount = 0;
            try
            {
                ApplicationContext.Current.Role = loginUserRole;
                ApplicationContext.Current.UserId = loginUserId;
                var query = this.FindBalances(sDate, eDate, target, balanceType, bank);
                recordCount = query.Count();
                pageCount = (recordCount + pageSize - 1) / pageSize;
                var q = query.Skip(pageSize * pageIndex).Take(pageSize);
                return q;
            }
            catch
            { }
            return null;
        }
        #endregion

        #region 权限
        /// <summary>
        /// 查找系统模块
        /// </summary>
        /// <param name="frmId"></param>
        /// <param name="frmParams"></param>
        /// <returns></returns>        
        public IEnumerable<TB_SysModule> FindSysModules(string frmId, string[] frmParams)
        {
            var predicate = PredicateBuilder.True<TB_SysModule>();
            if (frmId != null)
                predicate = predicate.And(p => p.frmId == frmId);

            if (frmParams != null)
            {
                foreach (string param in frmParams)
                    predicate = predicate.And(p => p.frmParams.Contains(param));
            }
            return dc.TB_SysModule.Where(predicate);
        }

        /// <summary>
        /// 根据特定条件查找用户操作权限
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="moduleId"></param>
        /// <param name="frmId"></param>
        /// <returns></returns>        
        public IEnumerable<TB_ActionPermission> FindActionPermissions(bool? isRole, int? ownerId, int? moduleId, string frmId)
        {
            try
            {
                var predicate = PredicateBuilder.True<TB_ActionPermission>();
                if (isRole != null)
                    predicate = predicate.And(p => p.isRole == isRole);

                if (ownerId != null)
                    predicate = predicate.And(p => p.ownerId == ownerId);

                if (moduleId != null)
                    predicate = predicate.And(p => p.moduleId == moduleId);
                else
                {
                    if (!string.IsNullOrEmpty(frmId))//通过frmId绑定moduleId
                    {
                        TB_SysModule obj = dc.TB_SysModule.SingleOrDefault(p => p.frmId == frmId);
                        if (obj != null)
                        {
                            predicate = predicate.And(p => p.moduleId == obj.id);
                        }
                    }
                }
                var query = from s in (dc.TB_ActionPermission.Where(predicate)) select s;
                return query;
            }
            catch { }
            return null;
        }

        /// <summary>
        /// 更新用户操作权限
        /// </summary>
        /// <param name="objs"></param>
        /// <returns></returns>        
        public bool UpdateActionPermission(int ownerId, int moduleId, TB_ActionPermission[] objs)
        {
            try
            {
                var query = from s in (dc.TB_ActionPermission.Where(p => p.ownerId == ownerId && p.moduleId == moduleId)) select s;
                if (query != null)
                {
                    dc.TB_ActionPermission.DeleteAllOnSubmit(query);
                    dc.SubmitChanges();
                }


                if (objs != null)
                {
                    dc.TB_ActionPermission.InsertAllOnSubmit(objs);
                    dc.SubmitChanges();
                }
                return true;
            }
            catch { }
            return false;
        }

        /// <summary>
        /// 查找角色模块关系
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public IEnumerable<TB_RoleModule> FindRoleModules(int? roleId)
        {
            var predicate = PredicateBuilder.True<TB_RoleModule>();
            if (roleId != null)
                predicate = predicate.And(p => p.roleId == roleId);
            return dc.TB_RoleModule.Where(predicate);
        }

        /// <summary>
        /// 更新角色模块关系
        /// </summary>
        /// <param name="roleId"></param>
        /// <param name="modules"></param>
        /// <returns></returns>
        public bool UpdateRoleModule(int roleId, IEnumerable<TB_RoleModule> objs)
        {
            try
            {
                var query = from s in (dc.TB_RoleModule.Where(p => p.roleId == roleId)) select s;
                if (query != null)
                    dc.TB_RoleModule.DeleteAllOnSubmit(query);

                if (objs != null)
                    dc.TB_RoleModule.InsertAllOnSubmit(objs);
                dc.SubmitChanges();
                return true;
            }
            catch { }
            return false;
        }
        #endregion

        #region 汇总统计
        [ContextReceivalBehavior(IsBidirectional = true)]
        public DataTable GetCountFee(DateTime sDate, DateTime eDate, int? bizMan)
        {
            try
            {
                eDate = eDate.Date.AddDays(1).AddSeconds(-1);
                EnumCommon.SysRoleType loginUserRole = (EnumCommon.SysRoleType)ApplicationContext.Current.Role;
                int loginUserId = ApplicationContext.Current.UserId;

                var units = dc.TB_Options.Where(p => p.type == EnumCommon.OptionType.币制单位.ToString());
                var feeTypeIdAry = Enum.GetValues(typeof(EnumCommon.FeeType));
                var feeTypeNameAry = Enum.GetNames(typeof(EnumCommon.FeeType));
                string ysKey = EnumCommon.FeeType.应收.ToString();
                string yfKey = EnumCommon.FeeType.应付.ToString();
                string ysId = ((int)EnumCommon.FeeType.应收).ToString();
                string yfId = ((int)EnumCommon.FeeType.应付).ToString();

                if (units.Any())
                {
                    StringBuilder sb = new StringBuilder();
                    List<string> selectFields = new List<string>();
                    List<string> joins = new List<string>();

                    int id = 1;
                    foreach (TB_Options o in units)
                    {
                        StringBuilder sb1 = new StringBuilder();
                        sb1.AppendLine("        ISNULL(tb" + id.ToString() + "." + ysKey + id.ToString() + ", 0) AS '" + ysKey + "(" + o.name + ")' ,");
                        sb1.AppendLine("        ISNULL(tb" + id.ToString() + "." + yfKey + id.ToString() + ", 0) AS '" + yfKey + "(" + o.name + ")' ,");
                        sb1.AppendLine("        ISNULL(tb" + id.ToString() + ".lr" + id.ToString() + ", 0) AS '利润(" + o.name + ")' ");
                        selectFields.Add(sb1.ToString());
                        sb1 = null;

                        sb1 = new StringBuilder();
                        //if (id == 1)
                        //    sb1.AppendLine("        LEFT OUTER JOIN ( SELECT [order] ,");
                        //else
                        //    sb1.AppendLine("        INNER JOIN ( SELECT [order] ,");
                        sb1.AppendLine("        LEFT OUTER JOIN ( SELECT [order] ,");
                        sb1.AppendLine("                            ISNULL([" + ysId + "], 0) AS '" + ysKey + id.ToString() + "' ,");
                        sb1.AppendLine("                            ISNULL([" + yfId + "], 0) AS '" + yfKey + id.ToString() + "' ,");
                        sb1.AppendLine("                            ISNULL([" + ysId + "], 0) - ISNULL([" + yfId + "], 0) AS 'lr" + id.ToString() + "'");
                        sb1.AppendLine("                     FROM   ( SELECT    [order] ,");
                        sb1.AppendLine("                                        SUM(amount) total ,");
                        sb1.AppendLine("                                        feeType");
                        sb1.AppendLine("                              FROM      dbo.TB_Fee");
                        sb1.AppendLine("                              WHERE     isVerify = 1");
                        sb1.AppendLine("                                        AND unit = " + o.id.ToString());
                        sb1.AppendLine("                              GROUP BY  [order] ,");
                        sb1.AppendLine("                                        feeType");
                        sb1.AppendLine("                            ) f" + id.ToString() + " PIVOT( SUM(total) FOR feeType IN ( [" + ysId + "], [" + yfId + "] ) ) AS pv" + id.ToString());
                        sb1.AppendLine("                   ) tb" + id.ToString() + " ON a.id = tb" + id.ToString() + ".[order]");
                        joins.Add(sb1.ToString());
                        sb1 = null;
                        id++;
                    }

                    sb.AppendLine("SELECT  a.userName as '业务员',");
                    sb.AppendLine("        a.code '工作单号',a.id 'OrderId',");
                    sb.AppendLine(string.Join(",", selectFields.ToArray()));
                    sb.AppendLine("FROM    ( SELECT    o.id ,");
                    sb.AppendLine("                    o.code ,");
                    sb.AppendLine("                    u.userName");
                    sb.AppendLine("          FROM      dbo.TB_Order o ,");
                    sb.AppendLine("                    dbo.TB_User u");
                    sb.AppendLine("          WHERE     (o.deleteFlag=0) AND (o.operator = u.id) AND (o.orderTime>='" + sDate.ToString("yyyy-MM-dd") + "' AND o.orderTime<='" + eDate.ToString("yyyy-MM-dd HH:mm:ss") + "')");

                    if (EnumCommon.SysRoleType.业务员 == loginUserRole)
                        sb.AppendLine("        AND (o.operator =" + loginUserId.ToString() + ")");

                    if (EnumCommon.SysRoleType.主管经理 == loginUserRole)
                        sb.AppendLine("        AND (o.operator =" + loginUserId.ToString() + " or u.leader=" + loginUserId.ToString() + ")");

                    if (bizMan != null)
                        sb.AppendLine("        AND (o.operator =" + bizMan.ToString() + ")");
                    sb.AppendLine("        ) a");
                    sb.AppendLine(string.Join(" ", joins.ToArray()));
                    sb.AppendLine(" order by a.userName,a.code");
                    return Common.GetSqlDataTable(dc.Connection.ConnectionString, sb.ToString());
                }
            }
            catch { }
            return null;
        }

        public DataTable GetCountFee2(DateTime sDate, DateTime eDate, int? bizMan, int loginUserId, int loginUserRole)
        {
            try
            {
                ApplicationContext.Current.Role = loginUserRole;
                ApplicationContext.Current.UserId = loginUserId;
                return this.GetCountFee(sDate, eDate, bizMan);
            }
            catch
            { }
            return null;
        }
        #endregion

        #region 菜单
        [ContextReceivalBehavior(IsBidirectional = true)]
        public IEnumerable<TB_SysModule> GetUserMenus()
        {
            EnumCommon.SysRoleType loginUserRole = (EnumCommon.SysRoleType)ApplicationContext.Current.Role;
            var predicate = PredicateBuilder.True<TB_SysModule>();
            predicate = predicate.And(p => p.display);

            if (EnumCommon.SysRoleType.管理员 == loginUserRole)
            {
                var query = from s in dc.TB_SysModule.Where(predicate)
                            select s;
                return query;
            }
            else
            {
                var query = from s in dc.TB_SysModule.Where(predicate)
                            join r in dc.TB_RoleModule.Where(p => p.roleId == (int)loginUserRole) on s.id equals r.moduleId
                            select s;
                return query;
            }
        }

        public IEnumerable<TB_SysModule> GetUserMenus2(int role, string frmId)
        {
            try
            {
                EnumCommon.SysRoleType loginUserRole = (EnumCommon.SysRoleType)role;
                var predicate = PredicateBuilder.True<TB_SysModule>();
                if (!string.IsNullOrEmpty(frmId))//若指定frmId则忽略display条件匹配它
                    predicate = predicate.And(p => p.frmId.ToLower() == frmId.ToLower());
                else
                    predicate = predicate.And(p => p.display);


                if (EnumCommon.SysRoleType.管理员 == loginUserRole)
                {
                    var query = from s in dc.TB_SysModule.Where(predicate)
                                select s;
                    return query;
                }
                else
                {
                    var query = from s in dc.TB_SysModule.Where(predicate)
                                join r in dc.TB_RoleModule.Where(p => p.roleId == (int)loginUserRole) on s.id equals r.moduleId
                                select s;
                    return query;
                }
            }
            catch { }
            return null;
        }
        #endregion

        #region 系统参数配置
        /// <summary>
        /// 获取系统参数配置
        /// </summary>
        /// <returns></returns>
        public IEnumerable<TB_SysParams> GetSysParams()
        { return from s in dc.TB_SysParams select s; }
        #endregion
    }
}
